<?php
//include APPLICATION_PATH."/models/base/ProvinceBase.php";
/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
class Model_Report3{
    
    public function fetchAllReport($fromdate, $todate, $product_ids="", $groupproduct_ids="", $agentbuyer_ids="", $agentseller_ids=""){
        $db = Zend_Db_Table::getDefaultAdapter();
        //$select=$db->select()
               // ->from('province')
                //->//where('status=1');
        if($fromdate=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(str_replace('/', '-', $fromdate));
        if($todate=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(str_replace('/', '-', $todate));
       
        $andWhere = "1=1";
        if(strlen($groupproduct_ids)>0 && strlen($product_ids)>0){
            $andWhere = "( product_category.id IN (".$groupproduct_ids.") OR order_detail.product_id IN (".$product_ids."))";
            
        }else if(strlen($groupproduct_ids)>0){
            $andWhere = "product_category.id IN (".$groupproduct_ids.")";
            
         }else if(strlen($product_ids)>0){
            $andWhere = "order_detail.product_id IN (".$product_ids.")";
            
        }else{
            
        }
        if(strlen($agentbuyer_ids)>0)
            $andWhere = $andWhere." and buyer.id IN (".$agentbuyer_ids.")";
         if(strlen($agentseller_ids)>0)
            $andWhere = $andWhere." and seller.id IN (".$agentseller_ids.")";
        $select = "SELECT buyer.name as buyer_name, province.name as province_name, order_detail.quantity, seller.province_id as seller_province,buyer.province_id as buyer_province,
                        product.name as product_name, order_detail.order_created, seller.name as seller_name, order_detail.charges
                        FROM  `order` 
                        INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                        INNER JOIN  `agent` AS buyer ON order.store_id = buyer.id
                        INNER JOIN  `agent` AS seller ON order_detail.agent_id = seller.id                        
                        LEFT JOIN `product` ON product.id = order_detail.product_id
                        LEFT JOIN  `product_category` ON product_category.id = product.product_cat_id
                        LEFT JOIN `province` ON province.id = buyer.province_id
                        WHERE order_detail.order_status_id = 2 and UNIX_TIMESTAMP(order_detail.order_created) >= ".$fromdate
                        ." and UNIX_TIMESTAMP(order_detail.order_created) <= ".$todate
                        . " and ".$andWhere."
                            ORDER BY order_detail.order_created DESC";
        $stmt=$db->query($select);
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
    public function fetchAllReportWithId($fromdate, $todate, $product_ids="", $groupproduct_ids="", $agentbuyer_ids="", $agentseller_ids=""){
        $db = Zend_Db_Table::getDefaultAdapter();
        //$select=$db->select()
               // ->from('province')
                //->//where('status=1');
        if($fromdate=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(str_replace('/', '-', $fromdate));
        if($todate=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(str_replace('/', '-', $todate));
       
        $andWhere = "1=1";
        if(strlen($groupproduct_ids)>0 && strlen($product_ids)>0){
            $andWhere = "( product_category.id IN (".$groupproduct_ids.") OR order_detail.product_id IN (".$product_ids."))";
            
        }else if(strlen($groupproduct_ids)>0){
            $andWhere = "product_category.id IN (".$groupproduct_ids.")";
            
         }else if(strlen($product_ids)>0){
            $andWhere = "order_detail.product_id IN (".$product_ids.")";
            
        }else{
            
        }
        if(strlen($agentbuyer_ids)>0)
            $andWhere = $andWhere." and buyer.id IN (".$agentbuyer_ids.")";
         if(strlen($agentseller_ids)>0)
            $andWhere = $andWhere." and seller.id IN (".$agentseller_ids.")";
        $select = "SELECT buyer.id as buyer_id,seller.id as seller_id,buyer.name as buyer_name, province.name as province_name, order_detail.quantity, seller.province_id as seller_province, buyer.province_id as buyer_province,
                        product.name as product_name, order_detail.order_created, seller.name as seller_name, order_detail.charges
                        FROM  `order` 
                        INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                        INNER JOIN  `agent` AS buyer ON order.store_id = buyer.id
                        INNER JOIN  `agent` AS seller ON order_detail.agent_id = seller.id                        
                        LEFT JOIN `product` ON product.id = order_detail.product_id
                        LEFT JOIN  `product_category` ON product_category.id = product.product_cat_id
                        LEFT JOIN `province` ON province.id = buyer.province_id
                        WHERE order_detail.order_status_id = 2 and UNIX_TIMESTAMP(order_detail.order_created) >= ".$fromdate
                        ." and UNIX_TIMESTAMP(order_detail.order_created) <= ".$todate
                        . " and ".$andWhere."
                            ORDER BY order_detail.order_created DESC";
        $stmt=$db->query($select);
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
    public function fetchAllReportExport($fromdate, $todate, $product_ids="", $groupproduct_ids="", $agentbuyer_ids="", $agentseller_ids=""){
        $db = Zend_Db_Table::getDefaultAdapter();
        //$select=$db->select()
               // ->from("province')
                //->//where('status=1');
        if($fromdate=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(str_replace('/', '-', $fromdate));
        if($todate=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(str_replace('/', '-', $todate));
        $andWhere = "1=1";
        if(strlen($groupproduct_ids)>0 && strlen($product_ids)>0){
            $andWhere = "( product_category.id IN (".$groupproduct_ids.") OR order_detail.product_id IN (".$product_ids."))";
            
        }else if(strlen($groupproduct_ids)>0){
            $andWhere = "product_category.id IN (".$groupproduct_ids.")";
            
         }else if(strlen($product_ids)>0){
            $andWhere = "order_detail.product_id IN (".$product_ids.")";
            
        }else{
            
        }
        if(strlen($agentbuyer_ids)>0)
            $andWhere = $andWhere." and buyer.id IN (".$agentbuyer_ids.")";
         if(strlen($agentseller_ids)>0)
            $andWhere = $andWhere." and seller.id IN (".$agentseller_ids.")";
        $select ="SELECT buyer.name as buyer_name, province.name as province_name, order_detail.quantity, seller.province_id as seller_province,buyer.province_id as buyer_province,
                        product.name as product_name, order_detail.order_created, seller.name as seller_name, order_detail.charges
                        FROM  `order` 
                        INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                        INNER JOIN  `agent` AS buyer ON order.store_id = buyer.id
                        INNER JOIN  `agent` AS seller ON order_detail.agent_id = seller.id
                        LEFT JOIN `product` ON product.id = order_detail.product_id
                        LEFT JOIN  `product_category` ON product_category.id = product.product_cat_id
                        LEFT JOIN `province` ON province.id = buyer.province_id
                        WHERE buyer.agent_status!=3 and order_detail.order_status_id = 2 and UNIX_TIMESTAMP(order_detail.order_created) >= ".$fromdate
                        ." and UNIX_TIMESTAMP(order_detail.order_created) <= ".$todate
                        . " and ".$andWhere        
                       ." ORDER BY order_detail.order_created DESC";
               $stmt=$db->query($select);
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
}

